package blacknWhite.Libraries;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

public class AppDatabase extends SQLiteOpenHelper {
	private static final String TAG = "MarketSalesMonitorDatabase";

	private static final String DATABASE_NAME = "MarketSalesMonitor.db";

	// NOTE: carefully update onUpgrade() when bumping database versions to make
	// sure user data is saved.

	// private static final int VER_LAUNCH = 1;
	private static final int VER_SESSION_HASHTAG = 9;

	private static final int DATABASE_VERSION = VER_SESSION_HASHTAG;

	public interface Tables {
		String ORDERS = "orders";
		String APP_COMMENTS = "app_comments";
	}
	

	public interface OrdersColumns {
		String ORDER_NUMBER = "order_number";
		int ORDER_NUMBER_ID = 1;
		String SALE_DATE = "sale_date";
		int SALE_DATE_ID = 2;
		String CURRENCY = "currency";
		int CURRENCY_ID = 3;
		String TOTAL = "total";
		int TOTAL_ID = 4;
		String STATUS = "status";
		int STATUS_ID = 5;
	}
	
	public interface AppCommentsColumns {
		String PACKAGE_ID = "package_id"; //1
		int PACKAGE_ID_ID = 1;
		String AUTHOR_NAME = "author_name"; //2
		int AUTHOR_NAME_ID = 2;
		String CREATION_TIME = "creation_time"; //3
		int CREATION_TIME_ID = 3;
		String RATING = "rating"; //4
		int RATING_ID = 4;
		String TEXT = "text"; //5
		int TEXT_ID = 5;
	}

	public AppDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			db.execSQL("CREATE TABLE " + Tables.ORDERS + " (" + BaseColumns._ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
					+ OrdersColumns.ORDER_NUMBER + " TEXT NOT NULL,"
					+ OrdersColumns.SALE_DATE + " INTEGER NOT NULL,"
					+ OrdersColumns.CURRENCY + " TEXT,"
					+ OrdersColumns.TOTAL + " REAL NOT NULL,"
					+ OrdersColumns.STATUS + " TEXT," + "UNIQUE ("
					+ OrdersColumns.ORDER_NUMBER + ") ON CONFLICT REPLACE)");

			db.execSQL("CREATE TRIGGER limit_orders_table AFTER INSERT ON "
					+ Tables.ORDERS + " BEGIN DELETE FROM " + Tables.ORDERS + " "
					+ "WHERE " + BaseColumns._ID + " NOT IN (SELECT "
					+ BaseColumns._ID + " FROM " + Tables.ORDERS + " ORDER BY "
					+ OrdersColumns.SALE_DATE + " DESC LIMIT 150);" + " END;");

			db.execSQL("CREATE TABLE " + Tables.APP_COMMENTS + " (" + BaseColumns._ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
					+ AppCommentsColumns.PACKAGE_ID + " TEXT NOT NULL,"
					+ AppCommentsColumns.AUTHOR_NAME + " TEXT NOT NULL,"
					+ AppCommentsColumns.CREATION_TIME + " INTEGER NOT NULL,"
					+ AppCommentsColumns.RATING + " REAL NOT NULL,"
					+ AppCommentsColumns.TEXT + " TEXT,"
					+ "UNIQUE ("
					+ AppCommentsColumns.CREATION_TIME + ") ON CONFLICT REPLACE)");

			db.execSQL("CREATE TRIGGER limit_app_comments_table AFTER INSERT ON "
					+ Tables.APP_COMMENTS + " BEGIN DELETE FROM " + Tables.APP_COMMENTS + " "
					+ "WHERE " + BaseColumns._ID + " NOT IN (SELECT "
					+ BaseColumns._ID + " FROM " + Tables.APP_COMMENTS + " ORDER BY "
					+ AppCommentsColumns.CREATION_TIME + " DESC LIMIT 150);" + " END;");
		} catch (SQLException e) {
			Utils.LogException(e);
		}

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		try {
			Log.d(TAG, "onUpgrade() from " + oldVersion + " to " + newVersion);

			// NOTE: This switch statement is designed to handle cascading database
			// updates, starting at the current version and falling through to all
			// future upgrade cases. Only use "break;" when you want to drop and
			// recreate the entire database.
			int version = oldVersion;

			if (version != DATABASE_VERSION) {
				Log.w(TAG, "Destroying old data during upgrade");

				db.execSQL("DROP TABLE IF EXISTS " + Tables.ORDERS);
				db.execSQL("DROP TABLE IF EXISTS " + Tables.APP_COMMENTS);
				onCreate(db);
			}
		} catch (SQLException e) {
			Utils.LogException(e);
		}
	}
}
